package com.cm367.teamc.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.faces.bean.ApplicationScoped;
import javax.faces.bean.ManagedBean;

import com.cm367.teamc.bean.User;

@ManagedBean
@ApplicationScoped
public class UserDAO {

	public User findUser(String userID, String password) throws SQLException {
		User user = null;
		Connection conn = BaseDAO.getConnection();
		PreparedStatement ps = conn
				.prepareStatement("select * from user where userid=? and password=?");
		ps.setString(1, userID);
		ps.setString(2, password);
		ResultSet rs = ps.executeQuery();
		if (rs.next()) {
			user = new User();
			user.setUserName(rs.getString("username"));
			user.setRole(rs.getString("role"));
			user.setDepartment(rs.getString("department"));
		}
		rs.close();
		ps.close();
		return user;
	}

	public boolean save(String userID, String password, String role,
			String userName, String department) throws SQLException {
		Connection conn = BaseDAO.getConnection();
		PreparedStatement ps = conn
				.prepareStatement("insert into user values(?,?,?,?,?)");
		ps.setString(1, userID);
		ps.setString(2, password);
		ps.setString(3, role);
		ps.setString(4, userName);
		ps.setString(5, department);
		ps.execute();
		ps.close();
		if (role.equals("User")) {
			String managerID = findManagerID(department);
			if (managerID != null) {
				doMapping(managerID, userID);
			}
		}
		return true;
	}

	public boolean existRole(String dept) throws SQLException {
		Connection conn = BaseDAO.getConnection();
		PreparedStatement ps = conn
				.prepareStatement("select count(*) from user where department=? and role=?");
		ps.setString(1, dept);
		ps.setString(2, "Manager");
		ResultSet rs = ps.executeQuery();
		rs.next();
		int count = rs.getInt(1);
		if (count == 0)
			return false;
		return true;
	}

	public boolean delete(String userID) throws SQLException {
		Connection conn = BaseDAO.getConnection();
		PreparedStatement ps = conn
				.prepareStatement("delete from user where userid=?");
		ps.setString(1, userID);
		ps.execute();
		ps.close();
		undoMapping(userID);
		return true;
	}

	public boolean validateUserID(String userID) {
		boolean flag = false;
		Connection conn = BaseDAO.getConnection();
		try {
			PreparedStatement ps = conn
					.prepareStatement("select * from user where userid=?");
			ps.setString(1, userID);
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				flag = true;
			}
			rs.close();
		} catch (SQLException e) {
			;
		}
		return flag;
	}

	public String findManagerID(String department) throws SQLException {
		Connection conn = BaseDAO.getConnection();
		PreparedStatement ps = conn
				.prepareStatement("select * from user where department=?");
		ps.setString(1, department);
		ResultSet rs = ps.executeQuery();
		if (rs.next()) {
			return rs.getString("userid");
		}
		rs.close();
		ps.close();
		return null;
	}

	private boolean doMapping(String managerID, String userID)
			throws SQLException {
		Connection conn = BaseDAO.getConnection();
		PreparedStatement ps = conn
				.prepareStatement("insert into mapping values(?,?)");
		ps.setString(1, managerID);
		ps.setString(2, userID);
		ps.execute();
		ps.close();
		return true;
	}

	private boolean undoMapping(String userID) throws SQLException {
		Connection conn = BaseDAO.getConnection();
		PreparedStatement ps = conn
				.prepareStatement("delete from mapping where userid=?");
		ps.setString(1, userID);
		ps.execute();
		ps.close();
		return true;
	}

	public List<User> findAllUser() throws SQLException {
		List<User> users = new ArrayList<User>();
		Connection conn = BaseDAO.getConnection();
		PreparedStatement ps = conn
				.prepareStatement("select * from user order by department");
		ResultSet rs = ps.executeQuery();
		while (rs.next()) {
			String userid = rs.getString("userid");
			String password = rs.getString("password");
			String role = rs.getString("role");
			String username = rs.getString("username");
			String dept = rs.getString("department");
			users.add(new User(userid, password, role, username, dept));
		}
		rs.close();
		ps.close();
		return users;
	}

}
